﻿using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System.Windows;
using ReflectionStudio.Core.FTPClient;
using DSTVKPOS;
namespace WPFExcelReport
{
    using System.Data.OracleClient;
    using System.IO;
    public class DSTVKPOSReport : BaseReport
    {
        bool IsFTP = true;
        string strCompany = "HLT";
        string strExcelFile = "";
        string sheetName = "";
        string Standard = "VKPOS";
        public bool IsOnAuditing = false;
        public bool IsSendForAuditing = false;
        string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\DST\DSTStandard.xls");
        public IntexReptServBLClient proxy;
        public OracleConnection OracleDB;
        Dictionary<string, int> qpplan = new Dictionary<string, int>();
        string printedqplan = "";
        public bool IsApproved = false;
        public FTPClient ftpClient;
        public string Shareroot = @"R:\";
        BaseFunction m_bf = null;
        SETDSTVKPOS setVK = new SETDSTVKPOS();
        public DSTVKPOSReport()
            : base()
        {
        }
        public DSTVKPOSReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
            strExcelFile = @"DST\DSTStandard.xls";
            m_bf = new BaseFunction(OracleDB, proxy);
        }
        public void SetTXTValue(string sgb, ref int x, int y)
        {
            int length = 0;
            string s1 = "";
            string s2 = "";
            try
            {
                s1 = sgb;
                exAgent.SetCellValue(x, y, s1);
                x++;
                return;
                length = sgb.Length;
                if (length <= 50)
                {
                    s1 = sgb;
                    exAgent.SetCellValue(x, y, s1);
                    x++;
                    return;
                }
                s1 = sgb.Substring(0, 50);
                exAgent.SetCellValue(x, y, s1);
                x++;
                s2 = sgb.Substring(50, length - 50);
                exAgent.SetCellValue(x, y, s2);
                x++;
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void FillInExcelData(ArrayList columns, DataSet setSoruce, int xStart = 1, int yStart = 0)
        {
            string msg = "";
            try
            {
                
                string src = "";
                string sgb = "";
                int x = xStart, y = yStart;
                bool newpage = true;
                bool firstpage = true;
                DataTable tbVKKOPF = setSoruce.Tables["VKKOPF"];
                DataTable tbVKPOS = setSoruce.Tables["VKPOS"];
                DataRow dwVKPOS1 = tbVKPOS.Rows[0];
                string datalink = "";
                string KRS;
                string LFD;
                string POS;
                string TXTISIND;
                int page = 0;
                int pagel = 50;
                int position = 0;
                int lastrow = 0;
                DataRow dwVKKOPF = tbVKKOPF.Rows[0];
                DataTable tbVKPBEST = new DataTable(), tbVKK07 = new DataTable();
                DataTable TTGAAUF = new DataTable();
                string VKPMEEH = "";
                int linestart = 0;
                int pagestart = 0;
                string printdate, orderdate = "";
                DataRow dwFinish = null, dwColor = null;
                DataRow dwWDECK = null, dwWASTAMM = null;
                string prdg = "";
                int positions = 0;
                int col = 0;
                m_bf.OracleDB = OracleDB;
                m_bf.proxy = proxy;
                #region============花型要求ANGE===================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "ANGE";
                var query = from dwTemp in setSoruce.Tables["TXTKON"].AsEnumerable()
                            where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                            select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }
                x = 907;
                y = 33;
                string text = "";
                int targetlines = 907;
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        targetlines++;
                        string ci = "TXT" + m_bf.Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd() + Environment.NewLine;
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                }
                exAgent.SetCellValue(x, y, text);
                if (targetlines > 914)
                {
                    exAgent.ShowRows(914, 1, System.Convert.ToInt16(914 + System.Math.Ceiling(System.Convert.ToDouble((targetlines - 914) / 2))), 1);
                }
                #endregion

                #region============颜色要求BEST====================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "BEST";
                query = from dwTemp in setSoruce.Tables["TXTKON"].AsEnumerable()
                        where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                        select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }
                x = 907;
                y = 48;
                text = "";
                targetlines = 907;
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        targetlines++;
                        string ci = "TXT" + m_bf.Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd() + Environment.NewLine;
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                }
                exAgent.SetCellValue(x, y, text);
                if (targetlines > 914)
                {
                    exAgent.ShowRows(914, 1, System.Convert.ToInt16(914 + System.Math.Ceiling(System.Convert.ToDouble((targetlines - 914) / 2))), 1);
                }
                #endregion

                #region============生产要求IVVT====================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "IVVT";
                query = from dwTemp in setSoruce.Tables["TXTKON"].AsEnumerable()
                        where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                        select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }
                x = 936;
                y = 19;
                text = "";
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        string ci = "TXT" + m_bf.Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd() + Environment.NewLine;
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                }
                exAgent.SetCellValue(x, y, text);
                #endregion

                #region============质量要求BEST===================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "LIEF";
                query = from dwTemp in setSoruce.Tables["TXTKON"].AsEnumerable()
                        where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                        select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }
                x = 936;
                y = 1;
                text = "";
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        string ci = "TXT" + m_bf.Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd() + Environment.NewLine;
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                }
                exAgent.SetCellValue(x, y, text);
                #endregion

                #region============成件要求RECH===================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "RECH";
                query = from dwTemp in setSoruce.Tables["TXTKON"].AsEnumerable()
                        where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                        select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }
                x = 936;
                y = 33;
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    x = 936;
                    y = 48;
                    //包装要求
                    src = dwTXTKON["TXT010"].ToString(); sgb = GetGB2312Value(src); SetTXTValue(sgb.TrimEnd(), ref x, y);
                    x = 936;
                    y = 33;
                    //匹长要求
                    src = dwTXTKON["TXT020"].ToString(); sgb = GetGB2312Value(src); SetTXTValue(sgb.TrimEnd(), ref x, y);
                    x = 937;
                    y = 33;
                    //交货数
                    src = dwTXTKON["TXT030"].ToString(); sgb = GetGB2312Value(src); SetTXTValue(sgb.TrimEnd(), ref x, y);
                }
                #endregion

                #region============其他要求BEST===================
                tbVKK07 = new DataTable();
                x = 939;
                y = 48;
                exAgent.SetCellValue(x, y, "染料要求：环保、禁偶氨");
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "LVOR";
                query = from dwTemp in setSoruce.Tables["TXTKON"].AsEnumerable()
                        where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                        select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }
                x = 939;
                y = 33;
                text = "";
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        string ci = "TXT" + m_bf.Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd() + Environment.NewLine;
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                    exAgent.SetCellValue(x, y, text);
                }
                #endregion

                #region=============VKKOPF==================
                x = 3;
                src = "工厂编号：" + dwVKKOPF["VKKAUFKRS"].ToString() + "/" + System.Convert.ToInt64(dwVKKOPF["VKKAUFLFD"].ToString());
                exAgent.SetCellValue(x, 1, src);

                string VKKKUNDNR = dwVKKOPF["VKKKUNDNR"].ToString();

                src = "客户编号：" + System.Convert.ToInt64(VKKKUNDNR).ToString();
                exAgent.SetCellValue(x, 12, src);

                src = "付款方式:" +GetGB2312Value(dwVKKOPF["ZABBEZ"].ToString());
                exAgent.SetCellValue(x, 19, src.Trim());

               string PROJNR=dwVKKOPF["VKKPROJNR"].ToString();
               PROJNR = GetPROJNR(PROJNR);
               exAgent.SetCellValue(x, 28, "项目:" + PROJNR);

                src = "负责人:" + dwVKKOPF["VKKSACHBE"].ToString();
                exAgent.SetCellValue(x, 32, src);
                VKPMEEH = "单位:" + dwVKPOS1["VKPMEEH"].ToString();
                exAgent.SetCellValue(x, 40, VKPMEEH);
                src = "跟单员:" + dwVKKOPF["VKKNEUBEN"].ToString();
                exAgent.SetCellValue(x, 44, src);
                #endregion
                string year = dwVKKOPF["VKKNEUDAT"].ToString().Substring(0, 4);
                string month = dwVKKOPF["VKKNEUDAT"].ToString().Substring(4, 2);
                string day = dwVKKOPF["VKKNEUDAT"].ToString().Substring(6, 2);
                orderdate = "下单日期：" + year + "/" + month + "/" + day;
                exAgent.SetCellValue(x, 48, orderdate);
                #region============图片链接===================
                /*POS = "000";
                query = from dwTemp in setSoruce.Tables["DLDPOS"].AsEnumerable()
                        where dwTemp["KRS"].ToString() == KRS
                        && dwTemp["LFD"].ToString() == LFD
                        && dwTemp["POS"].ToString() == POS
                        select dwTemp;
                foreach (DataRow dwTemp in query.Distinct())
                {
                    datalink = dwTemp["DLDPPFAD"].ToString().Trim() + dwTemp["DLDPNAME"].ToString().Trim();
                    exAgent.AddHyperLink(x, 1, datalink);
                }*/
                datalink = @"R:\" + KRS.Trim() + "\\" + year + "\\" + month + "\\" + KRS.Trim() + System.Convert.ToInt64(LFD).ToString();
                exAgent.AddHyperLink(x, 1, datalink);
                #endregion
  
                year = DateTime.Now.Year.ToString();
                month = DateTime.Now.Month.ToString();
                day = DateTime.Now.Day.ToString();

                printdate = "打印日期：" + year + "/" + month + "/" + day;
                exAgent.SetCellValue(944, 54, printdate);
                xStart = 4;
                x = xStart;
                foreach (DataRow dwVKPOS in tbVKPOS.Rows)
                {
                    positions++;
                    msg = "开始写入：" + position.ToString() + "行";
                    EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.RunningProgress);
                    x++;
                    KRS = dwVKPOS["VKPAUFKRS"].ToString();
                    LFD = dwVKPOS["VKPAUFLFD"].ToString();
                    POS = dwVKPOS["VKPAUFPOS"].ToString();
                    #region==================WDECK=====================
                    double warpdensity = 0;
                    double weftdensity = 0;
                    double widthf = 0;
                    query = from dwTemp in setSoruce.Tables["WDECK"].AsEnumerable()
                            where dwTemp["WDAUFKRS"].ToString() == dwVKPOS["VKPAUFKRS"].ToString()
                            && dwTemp["WDAUFLFD"].ToString() == dwVKPOS["VKPAUFLFD"].ToString()
                            && dwTemp["WDAUFPOS"].ToString() == dwVKPOS["VKPAUFPOS"].ToString()
                            select dwTemp;
                    foreach (DataRow dwTemp in query.Distinct())
                    {
                        dwWDECK = dwTemp;
                    }
                    if (dwWDECK == null)
                    {
                        msg = "请检查WDECK：" + KRS + LFD + POS;
                        EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.RunningProgress);
                        return;
                    }
                    string WDPRART = "", WDPRVAR = "", WDFARBE = "";
                    if (dwWDECK != null)
                    {
                        WDPRART = dwWDECK["WDPRART"].ToString();
                        WDPRVAR = dwWDECK["WDPRVAR"].ToString();
                        WDFARBE = dwWDECK["WDFARBE"].ToString();
                    }
                    #endregion

                    #region==================WASTAMM======================
                    query = from dwTemp in setSoruce.Tables["WASTAMM"].AsEnumerable()
                            where dwTemp["WAART"].ToString() == WDPRART
                            && dwTemp["WAVAR"].ToString() == WDPRVAR
                            select dwTemp;
                    foreach (DataRow dwTemp in query)
                    {
                        dwWASTAMM = dwTemp;
                    }
                    if (dwWASTAMM == null)
                    {
                        msg = "请检查对应品种信息：" + KRS + LFD + POS;
                        EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.RunningProgress);
                        return;
                    }
                    #endregion

                    #region=================QPZUORD========================
                    DataRow dwQPZUORD = null;
                    query = from dwTemp in setSoruce.Tables["QPZUORD"].AsEnumerable()
                            where dwTemp["QPZART"].ToString() == WDPRART
                            && dwTemp["QPZVAR"].ToString() == WDPRVAR
                            select dwTemp;
                    foreach (DataRow dwTemp in setSoruce.Tables["QPZUORD"].Rows)
                    {

                        if (dwTemp["QPZART"].ToString().Trim() == WDPRART.Trim() &&
                            dwTemp["QPZVAR"].ToString().Trim() == WDPRVAR.Trim())
                        {
                            dwQPZUORD = dwTemp;
                        }
                    }
                    if (dwQPZUORD == null)
                    {
                        msg = "请检查质检计划信息：" + KRS + LFD + POS;
                        EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.StopProgress);
                        MessageBox.Show(msg);
                    }
                    #endregion

                    #region================WAFARBEN========================

                    query = from dwTemp in setSoruce.Tables["WAFARBEN"].AsEnumerable()
                            where dwTemp["WAFART"].ToString() == WDPRART
                            && dwTemp["WAFVAR"].ToString() == WDPRVAR
                            && dwTemp["WAFZUSATZ"].ToString() == dwWDECK["WDBESCHICH"].ToString()
                            && dwTemp["WAFZUART"].ToString() == "B"
                            select dwTemp;
                    foreach (DataRow dwTemp in query.Distinct())
                    {
                        dwFinish = dwTemp;
                    }
                    query = from dwTemp in setSoruce.Tables["WAFARBEN"].AsEnumerable()
                            where dwTemp["WAFART"].ToString() == WDPRART
                            && dwTemp["WAFVAR"].ToString() == WDPRVAR
                            && dwTemp["WAFZUSATZ"].ToString().Trim() == WDFARBE.Trim()
                            && dwTemp["WAFZUART"].ToString() == "C"
                            select dwTemp;
                    foreach (DataRow dwTemp in query.Distinct())
                    {
                        dwColor = dwTemp;
                    }
                    if (dwFinish == null || dwColor == null)
                    {
                        msg = "请检查后整理方式或颜色的WS07信息：" + KRS + LFD + POS;
                        EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.StopProgress);
                        MessageBox.Show(msg);
                    }
                    #endregion

                    #region====================VKPOS=======================
                    col = 1;
                    POS = dwVKPOS["VKPAUFPOS"].ToString();
                    exAgent.SetCellValue(x, 1, System.Convert.ToInt64(POS));
                    //exAgent.SetCellValue(x + 1, 2, dwVKPOS["VKPPOSTYP"].ToString());
                    #endregion

                    #region================客人花号/品名======================
                    src = dwVKPOS["VKPKDARTNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDFARBNR"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    col = col + 2;//3
                    exAgent.SetCellValue(x, 3, sgb);
                    src = dwVKPOS["VKPART"].ToString().TrimEnd() + "-" + dwVKPOS["VKPFARBE"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    col = col + 6;
                    exAgent.SetCellValue(x, 9, sgb);
                    #endregion

                    #region=================图片链接=========================
                    /*query = from dwTemp in setSoruce.Tables["DLDPOS"].AsEnumerable()
                            where dwTemp["KRS"].ToString() == KRS
                            && dwTemp["LFD"].ToString() == LFD
                            && dwTemp["POS"].ToString() == POS
                            select dwTemp;
                    foreach (DataRow dwTemp in query.Distinct())
                    {
                        datalink = dwTemp["DLDPPFAD"].ToString().Trim() + dwTemp["DLDPNAME"].ToString().Trim();
                        exAgent.AddHyperLink(x, 9, datalink);
                    }*/
                    #endregion

                    #region===================花型===================
                    if (dwWASTAMM == null)
                        continue;
                    src = dwWASTAMM["WAFABLOM"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    col = col + 4;//13
                    exAgent.SetCellValue(x, col, sgb);
                    #endregion

                    #region================颜色===================
                    if (dwColor != null)
                    {
                        src = dwColor["WAFBEZ"].ToString();
                        sgb = GetGB2312Value(src);
                        exAgent.SetCellValue(x, col, WDFARBE.Trim() + sgb.Trim());
                    }
                    #endregion

                    #region================数量===================
                    col = col + 8;//21
                    src = dwVKPOS["VKPMENGE1"].ToString();
                    string VKPMENGE1 = Divide(src, 100);
                    exAgent.SetCellValue(x, col, VKPMENGE1);
                    if (VKPMEEH.Trim() == "YD")
                    {
                        VKPMENGE1 = (System.Math.Round(System.Convert.ToDouble(VKPMENGE1) * 0.9144, 0)).ToString();
                    }
                    #endregion

                    #region================组织===================
                    ///成品克重/SQM
                    col = col + 3;//24
                    src = dwWASTAMM["WAFGRQM"].ToString();
                    sgb = Divide(src, 1);
                    exAgent.SetCellValue(x, col, sgb);
                    //组织 WAPROGRP vwWASTAMM WAARTGRP
                    src = dwWASTAMM["WAARTGRP"].ToString();
                    src = "00" + (src + "                                     ").Substring(0, 21) + "V";
                    sgb = m_bf.GetDataFromSDDAT("0178", src);
                    sgb = GetGB2312Value(sgb);
                    col = col + 2;//26
                    exAgent.SetCellValue(x, col, sgb);
                    sgb = "";
                    src = dwWASTAMM["WAPROGRP"].ToString();
                    src = "00" + (src + "                                     ").Substring(0, 21) + "V";
                    sgb = m_bf.GetDataFromSDDAT("0196", src);
                    sgb = GetGB2312Value(sgb);

                    if (sgb.Trim() != "DST")
                    {
                        prdg += "-" + System.Convert.ToInt16(POS).ToString() + ":" + sgb + ";";
                    }
                    #endregion

                    #region================规格===================
    

                    src = dwWASTAMM["WAFDDIM"].ToString();
                    sgb = GetGB2312Value(src);
                    string WAFDDIM = sgb.ToString();
                    src = dwWASTAMM["WAFFDDIS"].ToString();
                    sgb = Divide(src, 100);
                    string WAFFDDIS = sgb.ToString();
                    src = dwWASTAMM["WAFFDDIK"].ToString();
                    sgb = Divide(src, 100);
                    string WAFFDDIK = sgb.ToString();
                    col = col + 7;//33
                    exAgent.SetCellValue(x, col, WAFFDDIK + "x" + WAFFDDIS);
                    warpdensity = System.Convert.ToDouble(WAFFDDIK);
                    weftdensity = System.Convert.ToDouble(WAFFDDIS);
                    #endregion

                    #region================成品门幅/有效门幅=============
                    double inc2cm = 2.54;
                    src = dwWASTAMM["WAFBRTU"].ToString();
                    sgb = Divide(src, 10);
                    double WAFBRTU = System.Math.Round(System.Convert.ToDouble(sgb) / inc2cm, 1);

                    src = dwWASTAMM["WAFBRTUT"].ToString();
                    sgb = Divide(src, 10);
                    double WAFBRTUT = System.Math.Round(System.Convert.ToDouble(sgb) / inc2cm, 1);

                    src = dwWASTAMM["WAFBRTL"].ToString();
                    sgb = Divide(src, 10);
                    double WAFBRTL = System.Math.Round(System.Convert.ToDouble(sgb) / inc2cm, 1);

                    src = dwWASTAMM["WAFBRTLT"].ToString();
                    sgb = Divide(src, 10);
                    double WAFBRTLT = System.Math.Round(System.Convert.ToDouble(sgb) / inc2cm, 1);
                    col = col + 3;//36
                    if (WAFBRTUT > 0)
                    {
                        exAgent.SetCellValue(x, 36, (WAFBRTU - WAFBRTUT) + "/" + (WAFBRTU + WAFBRTUT));
                    }
                    else
                    {
                        exAgent.SetCellValue(x, 36, WAFBRTU);
                    }
                    if (WAFBRTLT > 0)
                    {
                        exAgent.SetCellValue(x, 38, (WAFBRTL - WAFBRTLT) + "/" + (WAFBRTL + WAFBRTLT));
                    }
                    else
                    {
                        exAgent.SetCellValue(x, 38, WAFBRTL);
                    }
                    widthf = System.Convert.ToDouble(WAFBRTU) * inc2cm;
                    if (WAFDDIM == "INC")
                    {
                        //widthf = widthf * 2.54;
                    }
                    #endregion

                    #region ==============溢短装==================
                    src = dwVKPOS["VKPMGV"].ToString();
                    string VKPMGV = Divide(src, 100);
                    src = dwVKPOS["VKPMGB"].ToString();
                    string VKPMGB = Divide(src, 100);
                    string shortover;
                    //VKPMGV/VKPMENGE1+"~"VKPMGB/VKPMENGE1
                    decimal sh = System.Math.Round((System.Convert.ToDecimal(VKPMGV) / System.Convert.ToDecimal(VKPMENGE1) - 1) * 100, 0);
                    decimal ov = System.Math.Round((System.Convert.ToDecimal(VKPMGB) / System.Convert.ToDecimal(VKPMENGE1) - 1) * 100, 0);
                    shortover = sh.ToString() + "~" + ov.ToString();
                    col = col + 4;//40
                    exAgent.SetCellValue(x, col, shortover.ToString());
                    #endregion

                    #region =================折70W=====================
                    double ration70w = System.Convert.ToDouble(VKPMENGE1) * System.Convert.ToDouble(WAFFDDIS) / 70;
                    col = col + 2;//42
                    exAgent.SetCellValue(x, col, System.Math.Round(ration70w, 0).ToString());

                    #endregion

                    #region =================船样数量====================
                    //船样数量
                    exAgent.SetCellValue(x, 45, dwVKPOS["VKPZUSATZ"].ToString().Trim());
                    #endregion

                    #region ===================物测=====================
                    string QPZPNRKRS = "";
                    string QPZPLFDNR = "";
                    if (dwQPZUORD != null)
                    {
                        QPZPNRKRS = dwQPZUORD["QPZPNRKRS"].ToString().Trim();
                        QPZPLFDNR = dwQPZUORD["QPZPLFDNR"].ToString().Trim();
                        exAgent.SetCellValue(x, 48, QPZPNRKRS + "-" + System.Convert.ToInt64(QPZPLFDNR));
                    }
                    FillInQPPLAN(ref lastrow, setSoruce, dwVKPOS, dwQPZUORD);
                    printedqplan += QPZPNRKRS + QPZPLFDNR + ";";
                    //交期
                    #endregion

                    #region ===================后整====================
                    if (dwFinish != null)
                    {
                        src = dwFinish["WAFBEZ"].ToString();
                        sgb = GetGB2312Value(src);
                        exAgent.SetCellValue(x, 50, sgb.Trim());
                        src = dwWDECK["WDBESCHICH"].ToString();
                        sgb = GetGB2312Value(src);
                        exAgent.SetCellValue(x, 15, sgb.Trim());
                    }
                    #endregion

                    #region =================交期======================
                    exAgent.SetCellValue(x, 57, dwVKPOS["VKPLIEFTERM"].ToString());
                    //船样 
                    #endregion

                    #region =================船样交期======================
                    exAgent.SetCellValue(x, 60, dwVKPOS["VKPWVTERM"].ToString().Trim());
                    #endregion

                    #region================包装要求===================
                    string VKPVERPART, VKPAUFMART;
                    src = dwVKPOS["VKPVERPART"].ToString();
                    VKPVERPART = src;
                    /*
                    src = "00" + (src + "                                     ").Substring(0, 21) + "V";
                    sgb = m_bf.GetDataFromSDDAT("0209", src);
                    sgb = sgb + "                                                          ";
                    sgb = GetGB2312Value(sgb.Substring(0, 24).Trim());
                    VKPVERPART = sgb;*/
                    src = dwVKPOS["VKPAUFMART"].ToString();
                    //src = "00" + (src + "                                     ").Substring(0, 21) + "V";
                    //sgb = m_bf.GetDataFromSDDAT("0114", src);
                    //sgb = sgb + "                                                          ";
                    //sgb = GetGB2312Value(sgb.Substring(0, 27).Trim());
                    VKPAUFMART = src;
                    exAgent.SetCellValue(x, 17, VKPAUFMART.Trim() + "/" + VKPVERPART.Trim());
                    #endregion

                    #region================成份===================
                    //成份
                    /*
                    src = dwWASTAMM["WAFILLER1"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(919, 46, sgb);
                    */
                    #endregion


                    #region============YARN FROM WAPROF====================
                    //lenwefttotal纬纱总长度，lenwarptotal 经纱总长度
                    double lenwefttotal = 100 * (widthf * 0.01 * 100 * weftdensity / 2.54);
                    double lenwarptotal = 100 * (widthf * warpdensity / 2.54);
                    // WAPANFART 项目  QPMBEZ 描述 WAPME 单位 WAPMIN WAPMAX
                    #region ====Get Yarn=============
                    query = from dwTemp in setSoruce.Tables["WAPROF"].AsEnumerable()
                            where dwTemp["WAPART"].ToString() == WDPRART
                            && dwTemp["WAPVAR"].ToString() == WDPRVAR
                            && dwTemp["WAPWAKZ"].ToString() == "F"
                            select dwTemp;
                    int connt = 0;
                    string yarn = "";
                    double yarncount = 0;
                    double numyarn = 0;
                    string jw = "";

                    double yarnmeter = 0;
                    double jt = 0, wt = 0;
                    Queue weftyarn = new Queue();
                    Queue warpyarn = new Queue();
                    DataTable tbGAAUF = setVK.GAAUF.Copy();
                    DataRow dwGAAUF = tbGAAUF.NewRow();
                    string addedyarn = "";
                    string jwyarn = "";
                    string yarnname = "";
                    foreach (DataRow dwWAPROF in query.Distinct())
                    {
                        jw = dwWAPROF["WAPBIS"].ToString().Trim();
                        yarn = dwWAPROF["WAPANFART"].ToString();
                        //yarnname = GetGB2312Value(dwWAPROF["QPMBEZ"].ToString().Substring(15, 20).Trim());
                        yarnname = dwWAPROF["QPMBEZ"].ToString().Substring(15, 20).Trim();
                        jwyarn = jw + ";" + yarn + ";";
                        if (addedyarn.Contains(jwyarn))
                        {
                            continue;
                        }
                        addedyarn += jwyarn;

                        yarncount = System.Convert.ToDouble(dwWAPROF["QPMWBER"].ToString());
                        numyarn = System.Convert.ToDouble(dwWAPROF["WAPBEM"].ToString()) * 0.0001;
                        //meterpkg = m_bf.MTR1KG(yarncount, "NE");
                        dwGAAUF["GAMANDANT"] = "00";
                        dwGAAUF["GAAUFKRS"] = dwVKPOS["VKPAUFKRS"];
                        dwGAAUF["GAAUFLFD"] = dwVKPOS["VKPAUFLFD"];
                        dwGAAUF["GAAUFPOS"] = dwVKPOS["VKPAUFPOS"];
                        dwGAAUF["GAFILLER"] = dwVKPOS["VKPAUFPOS"];
                        double orderqty = System.Convert.ToDouble(VKPMENGE1);
                        dwGAAUF["GAZLLG"] = System.Math.Round(orderqty);
                        if (orderqty <= 2000)
                        {
                            dwGAAUF["GAZLLG"] = System.Math.Round(orderqty * 1.1, 0);
                        }
                        if (orderqty <= 5000 && orderqty > 2000)
                        {
                            dwGAAUF["GAZLLG"] = System.Math.Round(orderqty * 1.05, 0);
                        }
                        //dwGAAUF["GAZLLG"] = VKPMENGE1;//已经折算为米
                        //订单行数量小于2000，用纱量=订单行数量*百米用纱量/100*1.1，2000<=订单行数量<5000，用纱量=订单行数量*百米用纱量/100*1.05，其它情况，用纱量=订单行数量*百米用纱量/100*1。请回复确认
                        //                        	GAABF2	GAZBEDKG                           

                        dwGAAUF["GAART"] = yarn;
                        dwGAAUF["GAKG1"] = yarncount;
                        dwGAAUF["GASP1"] = numyarn;
                        dwGAAUF["GASA"] = jw;//J       
                        dwGAAUF["GANEUBEN"] = yarnname;//J       

                        if (jw == "J")
                        {
                            jt += numyarn;

                        }
                        else
                        {
                            wt += numyarn;
                        }
                        tbGAAUF.Rows.Add(dwGAAUF);
                        dwGAAUF = tbGAAUF.NewRow();
                    }
                    #endregion

                    #region======Print==========
                    double density = 0;
                    int yarnstart = x;
                    int ywc = 0, ypc = 0;
                    double totalends = 0;
                    double hyqty = 0, yqty = 0;
                    double qty = 0;//折算订单数量
                    string JS = "";
                    string WS = "";
                    double ratioj = 63.5, ratiow = 63.5;
                    //弹力布成品门幅是48/50”的，经向63.5改为56，纬向63.5改为66.5 ；门幅每增加1",系数也增加1”；
                    //（非弹力布基数是57/8“，系数基数是63.5“）
                    double widthbaselycra = 48.5;
                    double widthbasen = 57.5;
                    widthf = widthf / 2.54;
                    bool isLycra = false;
                    double widthdifference = widthf - widthbasen;
                    if (widthdifference < -2)
                    {
                        isLycra = true;
                    }
                    if (isLycra)
                    {
                        ratioj = 56;
                        ratiow = 66.5;
                        if (widthf - widthbaselycra > 0)
                        {
                            ratioj = 56 + widthf - widthbaselycra;
                            ratiow = 66.5 + widthf - widthbaselycra;
                        }
                    }
                    if (!isLycra)
                    {
                        ratioj = 63.5;
                        ratiow = 63.5;
                    }
                    double ratio = ratiow;
                    foreach (DataRow dwTemp in tbGAAUF.Rows)
                    {
                        jw = dwTemp["GASA"].ToString();
                        yarn = dwTemp["GAART"].ToString();
                        yarncount = System.Convert.ToDouble(dwTemp["GAKG1"].ToString());
                        numyarn = System.Convert.ToDouble(dwTemp["GASP1"].ToString());
                        density = weftdensity;
                        totalends = wt;
                        ratio = ratiow;
                        if (jw == "J")
                        {
                            totalends = jt;
                            density = warpdensity;
                            ratio = ratioj;
                        }
                        qty = System.Convert.ToDouble(dwTemp["GAZLLG"].ToString());
                        hyqty = System.Math.Round((density * numyarn / totalends) * yarncount * ratio, 2);
                        dwTemp["GAKG3"] = hyqty;
                        dwTemp["GAKG4"] = System.Math.Round(qty * hyqty * 0.01, 2);
                        if (jw == "J")
                        {
                            JS += dwTemp["GANEUBEN"].ToString().Trim() + "+";
                            ypc++;
                        }
                        else
                        {
                            WS += dwTemp["GANEUBEN"].ToString().Trim() + "+"; 
                            ywc++;
                        }
                    }
                    if (JS.Length > 3)
                    {
                        JS = JS + "!!!";
                        JS = JS.Replace("+!!!", "");
                    }
                    if (WS.Length > 3)
                    {
                        WS = WS + "!!!";
                        WS = WS.Replace("+!!!", "");
                    }
                    string construction= "(" + JS + ")" + "x" + "(" + WS + ")";
                    exAgent.SetCellValue(x, 28, GetGB2312Value(construction));
                    dwWASTAMM["WAFBEM"] = m_bf.Left(construction + "                                 ", 40);
                    UpdateWASTAMM(dwWASTAMM);
                    #endregion

                    TTGAAUF.Merge(tbGAAUF);
                    #endregion
                }

                #region================用纱量汇总====================
                var query1 = from dwGAAUF in TTGAAUF.AsEnumerable()
                             group dwGAAUF by new
                             {
                                 GAART = dwGAAUF["GAART"],
                                 GANEUBEN = dwGAAUF["GANEUBEN"],
                             }
                                 into g
                                 select new
                                 {
                                     Yarn = g.Key,
                                     GAKG4 = g.Sum(dwGAAUF => dwGAAUF.Field<decimal>("GAKG4")),
                                 };

                string Article;
                double GAKG4;
                x = 908;
                y = 1;
                string syarn = "";
                string lyarn = "";
                string yname = "";
                Dictionary<string, int> yarnplace = new Dictionary<string, int>();
                foreach (var varYarn in query1)
                {
                    syarn = varYarn.Yarn.GAART.ToString();
                    yname = varYarn.Yarn.GANEUBEN.ToString();
                    exAgent.SetCellValue(x, y, this.GetGB2312Value(yname));
                    yarnplace.Add(yname, x);
                    GAKG4 = System.Math.Round(System.Convert.ToDouble(varYarn.GAKG4) * 0.001, 4);
                    exAgent.SetCellValue(x, y + 5, "'" + GAKG4.ToString());
                    #region linetotal
                    lyarn = "";
                    var query2 = from dwGAAUF in TTGAAUF.AsEnumerable()
                                 where dwGAAUF["GAART"].ToString() == syarn
                                 group dwGAAUF by new
                                 {
                                     GAAUFPOS = dwGAAUF["GAAUFPOS"],
                                 }
                                     into g
                                     select new
                                     {
                                         Line = g.Key,
                                         GAKG3 = g.Sum(dwGAAUF => dwGAAUF.Field<decimal>("GAKG3")),
                                     };
                    string line;
                    foreach (var lineYarn in query2)
                    {
                        line = lineYarn.Line.GAAUFPOS.ToString();
                        lyarn += "-" + System.Convert.ToInt64(line).ToString() + ":" + lineYarn.GAKG3.ToString() + ";";
                    }
                    exAgent.SetCellValue(x, y + 15, "'" + lyarn);
                    #endregion
                    x++;
                }
                #endregion

                #region===============原纱要求IVDI===================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "IVDI";
                query = from dwTemp in setSoruce.Tables["TXTKON"].AsEnumerable()
                        where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                        select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }
                x = 908;
                y = 11;
                string supplier = "";
                foreach (KeyValuePair<string, int> kp in yarnplace)
                {
                    supplier = "";
                    yname = kp.Key + ":";
                    x = kp.Value;
                    foreach (DataRow dwTXTKON in tbVKK07.Rows)
                    {
                        foreach (DataColumn dc in tbVKK07.Columns)
                        {
                            src = dwTXTKON[dc.ColumnName].ToString(); sgb = GetGB2312Value(src);
                            if (sgb.Contains(yname))
                            {
                                supplier += sgb.Replace(yname, "").TrimEnd();
                            }
                        }
                    }
                    exAgent.SetCellValue(x, y, supplier);
                }
                #endregion
                ShowRows(positions + 4);
                exAgent.SetCellValue(940, 46, prdg);
            }
            catch (Exception ex)
            {
                msg = "有错误产生，请注意:";
                EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.StopProgress);
                Tracer.Error(ex.StackTrace, ex);
                MessageBox.Show(msg + ex.Message);
            }
            finally
            {
                msg = "指令表虽然已经生产，但请注意核对。";
                EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.RunningProgress);
            }
        }
 
        public string GetPROJNR(string PROJNR)
        {
            string result = System.Convert.ToInt16(PROJNR).ToString();
            BaseFunction m_bf = new BaseFunction(OracleDB, proxy);
            DataSet setResult;
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand(); 
                or1.CommandText = "SELECT * "
                    + " FROM PJKOPF"
                    + " WHERE PKOPROJNR='"+PROJNR+"'"
                    ;
                setResult = m_bf.Connect2Proxy(or1);
                foreach (DataRow dwTemp in setResult.Tables[0].Rows)
                {
                    result += GetGB2312Value(dwTemp["PKOBEZ"].ToString());
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return result;
        }
        private void FillInQPPLAN(ref int lastrow, DataSet setSource, DataRow dwVKPOS, DataRow dwQPZUORD)
        {
            try
            {
                if (dwQPZUORD == null)
                    return;
                BaseFunction m_bf = new BaseFunction();
                string PNRKRS = dwQPZUORD["QPZPNRKRS"].ToString();
                string PLFDNR = dwQPZUORD["QPZPLFDNR"].ToString();
                exAgent.SetCurrentSheet(2);
                string article = dwVKPOS["VKPART"].ToString().TrimEnd();
                if (printedqplan.Contains(PNRKRS + PLFDNR + ";"))
                {
                    int row = qpplan[PNRKRS + PLFDNR];
                    string exsit = exAgent.GetCellValue(row, 4).ToString();
                    if (!exsit.Contains(article))
                    {
                        exAgent.SetCellValue(row, 4, exsit + "," + article);
                    }
                    exAgent.SetCurrentSheet(1);
                    return;
                }

                DataTable QPPLAN = setSource.Tables["QPPLAN"];
                DataTable QPSTEP = setSource.Tables["QPSTEP"];
                DataRow dwQPPLAN = null, dwQPSTEP = null;
                var query = from dwTemp in QPPLAN.AsEnumerable()
                            where dwTemp["QPPPNRKRS"].ToString() == PNRKRS
                            && dwTemp["QPPPLFDNR"].ToString() == PLFDNR
                            select dwTemp;
                foreach (DataRow dwTemp in query.Distinct())
                {
                    dwQPPLAN = dwTemp;

                }
                if (dwQPPLAN == null)
                    return;
                int maxrow = 1;
                foreach (KeyValuePair<string, int> qp in qpplan)
                {
                    maxrow++;
                }
                lastrow = 100*(maxrow-1)+1;
                qpplan.Add(PNRKRS + PLFDNR, lastrow);
                exAgent.SetCellValue(lastrow, 4, "规格:" + article);
                exAgent.SetCellValue(lastrow, 3, PNRKRS + "-" + System.Convert.ToInt64(PLFDNR));
                exAgent.ShowRows(lastrow, 1, lastrow, 1);
                lastrow++;
                /*
                exAgent.SetCellValue(lastrow, 1, "步骤");
                exAgent.SetCellValue(lastrow, 2, "编码");
                exAgent.SetCellValue(lastrow, 3, "标准");
                exAgent.SetCellValue(lastrow, 4, "名称");
                exAgent.SetCellValue(lastrow, 5, "方法");
                exAgent.SetCellValue(lastrow, 6, "单位");
                exAgent.SetCellValue(lastrow, 7, "目标");
                exAgent.SetCellValue(lastrow, 8, "备注");*/

                query = from dwTemp in QPSTEP.AsEnumerable()
                        where dwTemp["QPSPNRKRS"].ToString() == PNRKRS
                        && dwTemp["QPSPLFDNR"].ToString() == PLFDNR
                        orderby dwTemp["QPSSTEP"]
                        select dwTemp;
                string processed = "";
                string src, sgb;
                foreach (DataRow dwTemp in query.Distinct())
                {
                    dwQPSTEP = dwTemp;
                    string QPSSTEP = dwQPSTEP["QPSSTEP"].ToString() + ";";
                    if (processed.Contains(QPSSTEP))
                    {
                        continue;
                    }
                    lastrow++;
                    exAgent.ShowRows(lastrow, 1, lastrow, 1);
                    src = dwQPSTEP["QPSSTEP"].ToString().TrimEnd();
                    string STEP = src;
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(lastrow, 1, sgb);
                    src = dwQPSTEP["QPSMERKM"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(lastrow, 2, sgb);
                    src = dwQPSTEP["QPMKURZB"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(lastrow, 3, sgb);
                    src = dwQPSTEP["QPMBEZ"].ToString();
                    sgb = GetGB2312Value(src.Substring(0, 15));
                    exAgent.SetCellValue(lastrow, 4, sgb);
                    sgb = GetGB2312Value(src.Substring(15, 35));
                    exAgent.SetCellValue(lastrow, 5, sgb);

                    src = dwQPSTEP["QPMMEEH"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(lastrow, 6, sgb);

                    src = dwQPSTEP["QPSNSWERT"].ToString().TrimEnd();
                    double QPSNSWERT = System.Convert.ToDouble(Divide(src, 1000));

                    src = dwQPSTEP["QPSNSUG"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    double QPSNSUG = System.Convert.ToDouble(Divide(src, 1000));

                    src = dwQPSTEP["QPSNSOG"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    double QPSNSOG = System.Convert.ToDouble(Divide(src, 1000));
                    double min = QPSNSWERT + QPSNSUG;
                    double max = QPSNSWERT + QPSNSOG;
                    exAgent.SetCellValue(lastrow, 7, min + "~" + max);

                    src = dwQPSTEP["QPSASUG"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(lastrow, 8, sgb);
                    #region ==========================TEXT========================
                    DataTable tbVKK07 = new DataTable();

                    string TXTISIND = PNRKRS + PLFDNR + STEP;
                    var query1 = from dwTXTKON in setSource.Tables["TXTKON"].AsEnumerable()
                                 where dwTXTKON["TXTIPIND"].ToString().Contains(TXTISIND)
                                 select dwTXTKON;
                    if (query1.Distinct().Count() > 0)
                    {
                        tbVKK07 = query1.Distinct().CopyToDataTable();
                    }
                    string text = "";
                    string processedtxt = "";
                    foreach (DataRow dwTXTKON in tbVKK07.Rows)
                    {
                        TXTISIND = dwTXTKON["TXTIPIND"].ToString();
                        if (processedtxt.Contains(TXTISIND + ";"))
                        {
                            break;
                        }
                        processedtxt += TXTISIND + ";";
                        for (int ti = 1; ti < 40; ti++)
                        {
                            string ci = "TXT" + m_bf.Right("00" + ti, 2) + "0";
                            src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                            if (sgb == "")
                                break;
                            text += sgb.TrimEnd();
                        }
                    }
                    exAgent.SetCellValue(lastrow, 8, text);
                    #endregion
                    /* src = dwQPSTEP["QPSASOG"].ToString().TrimEnd();
                     sgb = GetGB2312Value(src);
                     exAgent.SetCellValue(lastrow, 10, sgb);
                     */
                    processed += QPSSTEP;
                }
                exAgent.SetCurrentSheet(1);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

        }
        private void ShowRows(int x)
        {
            try
            {
                int HidedRows = 0;
                int rowstart = 1;
                int rowend = 1;
                int col1, col2, col3;

                var val1 = exAgent.GetCellValue(1, 1);

                rowstart = 17; rowend = x; col1 = 1; col2 = 1;
                for (int Rowi = rowstart; Rowi <= rowend; Rowi++)
                {
                    exAgent.ShowRows(Rowi, col1, Rowi, col2);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void CheckDirectory(string root, string sub, string year, string month, string day)
        {
            try
            {
                string drlocal = root;
                string dr0 = ""; 

                #region========================LOCAL=====================
                drlocal = Path.Combine(PathHelper.ApplicationPath, @"Report");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, "Sales");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                #endregion
                drlocal = root;
                drlocal = Path.Combine(drlocal, sub);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, year);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, month);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                drlocal = Path.Combine(drlocal, "临时");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public override void WriteExcelReport(string fileType)
        {
            try
            {
                #region==========准备程序===================
                string filename = "";
                string sheetName = "";
                string Standard = "VKPOSPR";
                strExcelFile = @"DST\DSTStandard.xls";
                string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\" + strExcelFile);
                DataRow dwVKKOPF = dsSource.Tables["VKKOPF"].Rows[0];
                string year = dwVKKOPF["VKKNEUDAT"].ToString().Substring(0, 4);
                string month = dwVKKOPF["VKKNEUDAT"].ToString().Substring(4, 2);
                string day = dwVKKOPF["VKKNEUDAT"].ToString().Substring(6, 2);
                string tempfile = "";
                string destfile = "";
                string filedate = year + month + day;
                string KRS = dsSource.Tables["VKPOS"].Rows[0]["VKPAUFKRS"].ToString().Trim();
                string LFD = dsSource.Tables["VKPOS"].Rows[0]["VKPAUFLFD"].ToString().Trim();
                long flfd = 0;
                flfd = System.Convert.ToInt64(LFD);
                string ordernumer = KRS.Trim() + flfd.ToString();
                #region=================检查路径======================
                string root = "";
                root = Path.Combine(PathHelper.ApplicationPath, @"Report\Sales");
                CheckDirectory(root, KRS, year, month, day);
                root = Path.Combine(PathHelper.ApplicationPath, @"Report\Sales\备份");
                CheckDirectory(root, KRS, year, month, day);
                if (!IsFTP)
                {
                    root = Shareroot;
                    CheckDirectory(root, KRS, year, month, day);
                    root = Shareroot + @"\备份";
                    CheckDirectory(root, KRS, year, month, day);
                    root = Shareroot + @"\临时";
                    CheckDirectory(root, KRS, year, month, day);
                    root = Shareroot + @"\已审批";
                    CheckDirectory(root, KRS, year, month, day);
                }
                #endregion
                exAgent.CheckWorkBook(ordernumer);
                #endregion
                if (IsFTP)
                {
                    FTPShareFile(KRS, year, month, day, ordernumer, fileType);
                    return;
                }
                ServerShareFile(KRS, year, month, ordernumer, fileType);

                #region HIDE
                /*
                #region=========一般用户===================
                if (!IsOnAuditing)
                {
                    #region==========EXCEL/预览=======================

                    #endregion
                    #region==========检查临时文件是否已经存在，存在则删除=======================
                    filename = Shareroot + @"临时\"+KRS + "\\" + year + "\\" + month + @"\" + ordernumer + ".xls"; ;
                    if (File.Exists(filename))
                    {
                        File.Delete(filename);
                    }
                    #endregion
                    #region==========产生文件，复制到服务器=======================
                    destfile = filename;
                    tempfile = CreateFile(ordernumer);
                    if (File.Exists(destfile))
                    {
                        File.Delete(destfile);
                    }
                    File.Move(tempfile, destfile);
                    #endregion
                    #region=========打开服务器上的文件进行浏览========================
                    if (fileType == "OpenExcel")
                    {
                        System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                    }
                    #endregion
                    return;
                }
                #endregion

                #region=========审核用户===================
                #region==========EXCEL/预览==============================
                if (!IsApproved)
                {
                    #region==========检查临时文件是否存在，存在则打开，不存在，则返回=============
                    filename = Shareroot + @"临时\" + KRS + "\\" + year + "\\" + month + @"\" + ordernumer + ".xls"; ;
                    if (File.Exists(filename))
                    {
                        System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                    }
                    else
                    {
                        MessageBox.Show("亲，跟单员尚未产生指令表。hold住，hold住。");
                    }
                    #endregion
                    return;
                }
                #endregion

                #region==========发布==============================

                #region=========检查临时文件是否存在，存在则移动备份===================
                filename = Shareroot + @"临时\" + KRS + "\\" + year + "\\" + month + @"\" + ordernumer + ".xls"; ;
                if (File.Exists(filename))
                {
                    destfile = Shareroot + @"备份\" + KRS + "\\" + year + "\\" + month + "\\" + ordernumer
                                + "_"
                                + DateTime.Now.ToShortDateString().Replace("/", "_")
                                + "_"
                                + DateTime.Now.ToShortTimeString().Replace(":", "_")
                        + "跟单初始版本.xls";
                    File.Move(filename, destfile);
                }
                #endregion

                #region=========检查是否已经审批过，存在则移动备份===================
                filename = Shareroot + KRS + "\\" + year + "\\" + month + "\\" + ordernumer + ".xls"; ;
                if (File.Exists(filename))
                {
                    destfile = Shareroot + @"备份\" + KRS + "\\" + year + "\\" + month + ordernumer + "_"
                                + DateTime.Now.ToShortDateString().Replace("/", "_")
                                + "_"
                                + DateTime.Now.ToShortTimeString().Replace(":", "_")
                                + ".xls";
                    File.Move(filename, destfile);
                }
                #endregion

                #region==========产生文件，复制到服务器=======================
                destfile = Shareroot + @"已审批\"+KRS + "\\" + year + "\\" + month + "\\" + ordernumer + ".xls";
                filename = destfile;
                tempfile = CreateFile(ordernumer);
                File.Move(tempfile, destfile);
                #endregion

                #region=========打开服务器上的文件进行浏览========================
                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
                #endregion
                #endregion
                #endregion
                 */
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void ServerShareFile(string KRS, string year, string month, string ordernumer, string fileType)
        {
            try
            {
                string destfile, tempfile;
                #region=========一般用户===================
                if (!IsOnAuditing)
                {
                    #region==========EXCEL/预览=======================

                    #endregion
                    #region==========检查临时文件是否已经存在，存在则删除=======================
                    filename = Shareroot + @"临时\" + KRS + "\\" + year + "\\" + month + @"\" + ordernumer + ".xls"; ;
                    if (File.Exists(filename))
                    {
                        File.Delete(filename);
                    }
                    #endregion
                    #region==========产生文件，复制到服务器=======================
                    destfile = filename;
                    tempfile = CreateFile(ordernumer);
                    if (File.Exists(destfile))
                    {
                        File.Delete(destfile);
                    }
                    File.Move(tempfile, destfile);
                    #endregion
                    #region=========打开服务器上的文件进行浏览========================
                    if (fileType == "OpenExcel")
                    {
                        System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                    }
                    #endregion
                    return;
                }
                #endregion

                #region=========审核用户===================
                #region==========EXCEL/预览==============================
                if (!IsApproved)
                {
                    #region==========检查临时文件是否存在，存在则打开，不存在，则返回=============
                    filename = Shareroot + @"临时\" + KRS + "\\" + year + "\\" + month + @"\" + ordernumer + ".xls"; ;
                    if (File.Exists(filename))
                    {
                        System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                    }
                    else
                    {
                        MessageBox.Show("亲，跟单员尚未产生指令表。hold住，hold住。");
                    }
                    #endregion
                    return;
                }
                #endregion

                #region==========发布==============================

                #region=========检查临时文件是否存在，存在则移动备份===================
                filename = Shareroot + @"临时\" + KRS + "\\" + year + "\\" + month + @"\" + ordernumer + ".xls"; ;
                if (File.Exists(filename))
                {
                    destfile = Shareroot + @"备份\" + KRS + "\\" + year + "\\" + month + "\\" + ordernumer
                                + "_"
                                + DateTime.Now.ToShortDateString().Replace("/", "_")
                                + "_"
                                + DateTime.Now.ToShortTimeString().Replace(":", "_")
                        + "跟单初始版本.xls";
                    File.Move(filename, destfile);
                }
                #endregion

                #region=========检查是否已经审批过，存在则移动备份===================
                filename = Shareroot + KRS + "\\" + year + "\\" + month + "\\" + ordernumer + ".xls"; ;
                if (File.Exists(filename))
                {
                    destfile = Shareroot + @"备份\" + KRS + "\\" + year + "\\" + month + ordernumer + "_"
                                + DateTime.Now.ToShortDateString().Replace("/", "_")
                                + "_"
                                + DateTime.Now.ToShortTimeString().Replace(":", "_")
                                + ".xls";
                    File.Move(filename, destfile);
                }
                #endregion

                #region==========产生文件，复制到服务器=======================
                destfile = Shareroot + @"已审批\" + KRS + "\\" + year + "\\" + month + "\\" + ordernumer + ".xls";
                filename = destfile;
                tempfile = CreateFile(ordernumer);
                File.Move(tempfile, destfile);
                #endregion

                #region=========打开服务器上的文件进行浏览========================
                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
                #endregion
                #endregion
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void UpdateWASTAMM(DataRow dwData)
        {
            try
            {
                OracleCommand or1;
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "UPDATE WASTAMM SET WAFBEM=:WAFBEM "
                    + " WHERE  "
                    + "  WAMANDANT=:WAMANDANT AND WAART=:WAART AND WAVAR=:WAVAR";

                or1.Parameters.Add("WAMANDANT", OracleType.NVarChar).Value = dwData["WAMANDANT"].ToString();
                or1.Parameters.Add("WAART", OracleType.NVarChar).Value = dwData["WAART"].ToString();
                or1.Parameters.Add("WAVAR", OracleType.NVarChar).Value = dwData["WAVAR"].ToString();
                or1.Parameters.Add("WAFBEM", OracleType.NVarChar).Value = dwData["WAFBEM"].ToString();
                m_bf.Connect2Proxy(or1);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void CheckFTPDir(string root, string sub, string KRS, string year, string month, string day,string ordernumber="")
        {
            string createdtimefile = PathHelper.ApplicationPath + "\\ftp.txt";
            string newfile = "/创建于" + year + month + day;
            try
            {
                string drlocal = root;
                  if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                }
                drlocal = drlocal + "/" + sub;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                }
                drlocal = drlocal + "/" + KRS;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                }
                drlocal = drlocal + "/" + year;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                }
                drlocal = drlocal + "/" + month;
                if (!ftpClient.DirectoryExists(drlocal))
                {
                    ftpClient.CreateDirectory(drlocal);
                    ftpClient.PutFile(createdtimefile, drlocal + newfile);
                }
                if (ordernumber != "")
                {
                    drlocal = drlocal + "/" + ordernumber;
                    if (!ftpClient.DirectoryExists(drlocal))
                    {
                        ftpClient.CreateDirectory(drlocal);
                        ftpClient.PutFile(createdtimefile, drlocal + newfile);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private void FTPShareFile(string KRS, string year, string month, string day, string ordernumer, string fileType)
        {
            string localFile = @"C:\Windows\Temp\" + ordernumer + ".xls";
            string tempfile = "";
            try
            {
                ftpClient.Open();
                ftpClient.Login();
                string root = "/Sales/";
                ftpClient.SetCurrentDirectory("/");
                CheckFTPDir("/Sales", "临时", KRS, year, month, day);
                ftpClient.SetCurrentDirectory("/");
                CheckFTPDir("/Sales", "已审批", KRS, year, month, day);
                ftpClient.SetCurrentDirectory("/");
                CheckFTPDir("/Sales", "备份", KRS, year, month, day);
                ftpClient.SetCurrentDirectory("/");
                CheckFTPDir("/Sales", "OrderImages", KRS, year, month, day,ordernumer);                
                string destfile;
                #region=========一般用户===================
                if (!IsOnAuditing)
                {
                    #region==========EXCEL/预览=======================

                    #endregion
                    #region==========检查临时文件是否已经存在，存在则删除=======================
                    filename = root + "临时/" + KRS + "/" + year + "/" + month + "/" + ordernumer + ".xls"; ;

                    if (ftpClient.FileExists(filename))
                    {
                        ftpClient.RemoveFile(filename);
                    }
                    #endregion
                    #region==========产生文件，复制到服务器=======================
                    destfile = filename;
                    tempfile = CreateFile(ordernumer);
                    localFile = tempfile;
                    if (IsSendForAuditing)
                    {
                        if (ftpClient.FileExists(destfile))
                        {
                            ftpClient.RemoveFile(destfile);
                        }
                        ftpClient.PutFile(tempfile, destfile);
                        localFile = @"C:\Windows\Temp\" + ordernumer + ".xls";
                        ftpClient.GetFile(filename, localFile, false);
                    }
                    #endregion
                    #region=========打开服务器上的文件进行浏览========================
                    if (fileType == "OpenExcel")
                    {
                        localFile = @"C:\Windows\Temp\" + ordernumer + ".xls";
                        if (File.Exists(localFile))
                        {
                            File.Delete(localFile);
                        }
                        File.Move(tempfile, localFile);
                        System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
                    }
                    #endregion
                    return;
                }
                #endregion

                #region=========审核用户===================
                #region==========EXCEL/预览==============================
                localFile = @"C:\Windows\Temp\" + ordernumer + ".xls";
                if (!IsApproved)
                {
                    #region==========检查临时文件是否存在，存在则打开，不存在，则返回=============
                    filename = root + "临时/" + KRS + "/" + year + "/" + month + "/" + ordernumer + ".xls"; ;
                    if (ftpClient.FileExists(filename))
                    {
                        ftpClient.GetFile(filename, localFile, false);
                        System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
                    }
                    else
                    {
                        MessageBox.Show("亲，跟单员尚未产生指令表。hold住，hold住。");
                    }
                    #endregion
                    return;
                }
                #endregion

                #region==========发布==============================

                #region=========检查临时文件是否存在，存在则移动备份===================
                filename = root + "临时/" + KRS + "/" + year + "/" + month + "/" + ordernumer + ".xls"; ;
                if (ftpClient.FileExists(filename))
                {
                    destfile = root + "备份/" + KRS + "/" + year + "/" + month + "/" + ordernumer
                              + "_"
                              + DateTime.Now.ToShortDateString().Replace("/", "_")
                              + "_"
                              + DateTime.Now.ToShortTimeString().Replace(":", "_")
                      + "跟单初始版本.xls";
                    ftpClient.RenameFile(filename, destfile);
                }
                #endregion

                #region=========检查是否已经审批过，存在则移动备份===================
                filename = root + "已审批/" + KRS + "/" + year + "/" + month + "/" + ordernumer + ".xls"; ;
                if (ftpClient.FileExists(filename))
                {
                    destfile = root + "备份/" + KRS + "/" + year + "/" + month + "/" + ordernumer + "_"
                                + DateTime.Now.ToShortDateString().Replace("/", "_")
                                + "_"
                                + DateTime.Now.ToShortTimeString().Replace(":", "_")
                                + ".xls";
                    ftpClient.RenameFile(filename, destfile);
                }
                #endregion

                #region==========产生文件，复制到服务器=======================
                tempfile = CreateFile(ordernumer);
                destfile = root + "已审批/" + KRS + "/" + year + "/" + month + "/" + ordernumer + ".xls";
                ftpClient.PutFile(tempfile, destfile);
                localFile = @"C:\Windows\Temp\" + ordernumer + ".xls";
                //File.Move(tempfile, localFile);
                #endregion

                #region=========打开服务器上的文件进行浏览========================
                if (fileType == "OpenExcel")
                {
                    ftpClient.GetFile(filename, localFile, false);
                    System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
                }
                #endregion
                #endregion
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
        private string CreateFile(string orderno)
        {
            string filename = "";
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent.Open(filePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                exAgent.CreateNewSheet(orderno + ".xls", Standard+"PR", sheetName, pwd);
                filename = exAgent.FileName;
                //exAgent.SetCurrentSheet(2);
                //exAgent.RenameSheet("物测计划");
                exAgent.SetCurrentSheet(1);
                FillInExcelData(columns, dsSource, 0, 0);
                exAgent.Save();
                exAgent.Close();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }
    }
}
